﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DBBase;
using System.IO;
using System.Xml;
using System.Threading;
using System.Web;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using FastReport;

namespace Business
{
   
    public partial class Chargemanagement : Form
    {
        string Url = "";
        string lsh = "";
        string zbbh = "";
        float sum = 0;
        Report frt = new Report();
        public MainForm m_mainform;
        public Chargemanagement()
        {
            InitializeComponent();
            inicbx(comboBox3, "table_jzdw");
           
        }
        private void but_daochu_Click(object sender, EventArgs e)
        {
            string path = "";
            path = Application.StartupPath + "\\统计车辆数据" + "\\" + DateTime.Now.ToString("yyyyMMdd")  + ".xls";
            excel(path);
        }
        private void excel(string path)
        {
            try
            {
                StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();

                sb.Append("统计时间从" + date_kaishi.Value.ToShortDateString() + "至" + date_jieshu.Value.ToShortDateString() + "\t");
                sb.Append(Environment.NewLine);
                for (int i = 0; i < lv.Columns.Count; i++)
                    sb.Append(lv.Columns[i].Text.ToString() + "\t");
                sb.Append(Environment.NewLine);

                foreach (ListViewItem li in lv.Items)
                {
                    for (int i = 0; i < lv.Columns.Count; i++)
                        if (i == 2)
                        {
                            sb.Append("'" + li.SubItems[i].Text + "\t");
                        }
                        else
                        {
                            sb.Append(li.SubItems[i].Text + "\t");
                        }
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                MessageBox.Show("成功保存于" + path + "文件！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }   
        void inicbx(ComboBox cbx, string dataname)
        {
            Query objquery = new Query();
            string ssqlex = "";
            ssqlex = string.Format(@"select * from {0} order by table_1", dataname);
            DataTable dt = objquery.GetTable(ssqlex);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cbx.Items.Add(dt.Rows[i]["table_2"].ToString().Trim());
                if (i == 0)
                    cbx.Text = "";
            }
        }

        private void but_tongji_Click(object sender, EventArgs e)
        {
            sum = 0;
            lv.Items.Clear();
            Query mquery = new Query();
            string sql;
            sql = string.Format(@"SELECT AUTO.*,Detects_SJA.* from AUTO,Detects_SJA where AUTO.AT_ID=Detects_SJA.AT_ID and Detects_SJA.DET_JYCS=1");  
            if (radioButton1.Checked)
            {
                sql = sql + string.Format(@" and Detects_SJA.DET_DATE>'{0}' and Detects_SJA.DET_DATE<'{1}'", date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");
            }
            if (radioButton2.Checked)
            {
                sql = sql + string.Format(@" and Detects_SJA.DET_KPRQ>'{0}' and Detects_SJA.DET_KPRQ<'{1}'", date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");
            }
            if (comboBox1.Text.Contains("已"))
            {
                sql = sql + "and  Detects_SJA.DET_SFKP=1";
            }
            else if (comboBox1.Text.Contains("未"))
            {
                sql = sql + "and  Detects_SJA.DET_SFKP=0";
            }
            if (comboBox2.Text.Length > 0)
            {
                sql = sql + string.Format(@" and Detects_SJA.DET_SFFS='{0}' ", comboBox2.Text.Trim());
            }
            if (comboBox3.Text.Length > 0)
            {
                sql = sql + string.Format(@" and Detects_SJA.DET_JZDW='{0}' ", comboBox3.Text.Trim());
            }
            if (radioButton2.Checked)
            {
                sql = sql + "order by Detects_SJA.DET_BH asc ";
            }
            else
            {
                sql = sql + "order by Detects_SJA.DET_BH desc  ";
            }
             DataTable dt = mquery.GetTable(sql);
             if (dt.Rows.Count > 0)
             {
                 string[] exid = new string[5000];
                 string[] hp = new string[5000];
                 bool td = false;
                 int j = 0;
                 
                 for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     td = false;
                     exid[i] = dt.Rows[i]["AT_ID"].ToString().Trim();
                     hp[i] = dt.Rows[i]["AT_HPHM"].ToString().Trim();
                     if (i > 0)
                     {
                         for (int k = 0; k < i; k++)
                         {
                             if (exid[k].Equals(exid[i]))
                             {
                                 td = true;
                                 break;
                             }
                             if (hp[k].Equals(hp[i]))
                             {
                                 td = true;
                                 break;
                             }

                         }
                        if(!td)
                            j++;

                     }
                     if (td)
                         continue;

                    
                    ListViewItem lt = new ListViewItem((j + 1).ToString());
                    lt.SubItems.Add(dt.Rows[i]["DETECT_ID"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_BH"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["AT_HPHM"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_SFXM"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_DATE"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["AT_SYR"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_SFKP"].ToString().Trim().Contains("1") ? "已开票" : "未开票");
                    if (!dt.Rows[i]["DET_SFKP"].ToString().Trim().Contains("1"))
                    {
                        gxkpzt(dt.Rows[i]["DETECT_ID"].ToString().Trim());
                    }
                    lt.SubItems.Add(dt.Rows[i]["DET_SFFS"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_SFJE"].ToString().Trim());
                    sum += dt.Rows[i]["DET_SFJE"].ToString().Trim().Length > 0 ? float.Parse(dt.Rows[i]["DET_SFJE"].ToString().Trim()) : 0;
                    lt.SubItems.Add(dt.Rows[i]["DET_KPRQ"].ToString().Trim());
                    this.lv.Items.Add(lt);
                     
                    
                 }
             }
             textBox3.Text = sum.ToString();
        }

        private void lv_MouseUp(object sender, MouseEventArgs e)
        {
            checkBox1.Checked = false;
            checkBox2.Checked = false;
            checkBox3.Checked = false;
            checkBox4.Checked = false;
            checkBox5.Checked = false;
            foreach (ListViewItem item in lv.SelectedItems)
            {
                lsh = item.SubItems[1].Text.ToString().Trim();
                zbbh = item.SubItems[2].Text.ToString().Trim();
                text_hphm.Text = item.SubItems[3].Text.ToString().Trim();
                com_sffs.Text = item.SubItems[8].Text.ToString().Trim();
                text_sfje.Text = item.SubItems[9].Text.ToString().Trim();
                if (item.SubItems[4].Text.ToString().Trim().Contains("安检"))
                {
                    checkBox1.Checked=true;
                }
                if (item.SubItems[4].Text.ToString().Trim().Contains("综检"))
                {
                    checkBox2.Checked=true;
                }
                if (item.SubItems[4].Text.ToString().Trim().Contains("环保"))
                {
                    checkBox3.Checked = true;
                }
                if (item.SubItems[4].Text.ToString().Trim().Contains("临时"))
                {
                    checkBox4.Checked = true;
                }
                if (item.SubItems[4].Text.ToString().Trim().Contains("报废"))
                {
                    checkBox5.Checked = true;
                }
               
            }
        }

        private void com_sffs_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (com_sffs.Text.Contains("记账") || com_sffs.Text.Contains("对公"))
            {
                com_jzdw.Items.Clear();
                inicbx(com_jzdw, "table_jzdw");
            }
            else
            {
                com_jzdw.Text = "";
            }
        }

        private void lv_MouseClick(object sender, MouseEventArgs e)
        {
            try
            {
                if (e.Button == MouseButtons.Right)
                {
                    Point p = new Point(e.X, e.Y);
                    this.contextMenuStrip1.Tag = this.lv.SelectedItems[0];
                    contextMenuStrip1.Show(lv, p);
                }
            }
            catch
            {

            }
        }

       

        private void button2_Click(object sender, EventArgs e)
        {
            string jyxm = "";
            if (text_sfje.Text.Length < 1)
            {
                text_sfje.Text = "0.00";
            }
            if (checkBox1.Checked)
            {
                jyxm = "安检";
            }
            if (checkBox2.Checked)
            {
                if (jyxm.Trim().Length > 0)
                {
                    jyxm = jyxm + ",综检";
                }
                else
                {
                    jyxm = "综检";
                }
               
            }
            if (checkBox3.Checked)
            {
                if (jyxm.Trim().Length > 0)
                {
                    jyxm = jyxm + ",环保";
                }
                else
                {
                    jyxm = "环保";
                }
            }
            if (checkBox4.Checked)
            {
                if (jyxm.Trim().Length > 0)
                {
                    jyxm = jyxm + ",临时检测";
                }
                else
                {
                    jyxm = "临时检测";
                }
            }
            if (checkBox5.Checked)
            {
                if (jyxm.Trim().Length > 0)
                {
                    jyxm = jyxm + ",报废车";
                }
                else
                {
                    jyxm = "报废车";
                }
            }
            Query mquery = new Query();
            string ssql;
            ssql = string.Format(@"Select * From StationInfor ");     
            string bh = mquery.GetDBString(ssql, "SI_LSHKT");

            string sql = string.Format(@"select * from  Detects_SJA where DET_BH!=''order by  DET_BH desc");
            DataTable dt = mquery.GetTable(sql);
            int lsbh = 0;
            if (dt.Rows.Count > 0)
            {
                int.TryParse(dt.Rows[0]["DET_BH"].ToString(), out lsbh);
            }
            string jylsh = "";
            if (lsbh.ToString().Length > 6)
            {
                 jylsh = string.Format("0{0}",(lsbh + 1).ToString());//检验流水号
            }
            else
            {
                 jylsh = string.Format("{0}{1}", bh, (dt.Rows.Count + 1).ToString("000000"));//检验流水号
            }
            if (zbbh.Trim().Length > 0)
            {
                sql = string.Format(@"update Detects_SJA set DET_SFFS='{0}',DET_SFKP='{1}',DET_SFJE='{2}',DET_JZDW='{3}',DET_KPRQ='{4}',DET_SFXM='{5}' where DETECT_ID='{6}'",
                                      com_sffs.Text.Trim(), "1", float.Parse(text_sfje.Text).ToString("0.00").Trim(), com_jzdw.Text.Trim(), DateTime.Now, jyxm.Trim(), lsh);
            }
            else
            {
                sql = string.Format(@"update Detects_SJA set DET_BH='{0}',DET_SFFS='{1}',DET_SFKP='{2}',DET_SFJE='{3}',DET_JZDW='{4}',DET_KPRQ='{5}',DET_SFXM='{6}' where DETECT_ID='{7}'",
                                      jylsh, com_sffs.Text.Trim(), "1", float.Parse(text_sfje.Text).ToString("0.00").Trim(), com_jzdw.Text.Trim(), DateTime.Now, jyxm.Trim(), lsh);
            }
            mquery.UpdateTable(sql);

            but_tongji_Click(sender, e);

        }
        private void 预览票据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                Url = System.Windows.Forms.Application.StartupPath + "\\收费.frx";
                frt.Load(Url);
                frt.RegisterData(PrintData(lsh), "new");
                frt.GetDataSource("new").Enabled = true;
                frt.Show();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        public DataTable PrintData(string slsh)//打开报告单
        {
            string sql = string.Format(@"SELECT AUTO.*,Detects_SJA.*,StationInfor.* from AUTO,Detects_SJA,StationInfor where AUTO.AT_ID=Detects_SJA.AT_ID  and Detects_SJA.DETECT_ID='{0}'", lsh);
            Query mqury = new Query();
            DataTable dt = new DataTable();
            dt = mqury.GetTable(sql);
            dt.TableName = "new";//数据源表名
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("year");
                dt.Rows[0][dt.Columns.Count - 1] = DateTime.Parse(dt.Rows[0]["DET_KPRQ"].ToString()).ToString("yyyy");
                dt.Columns.Add("month");
                dt.Rows[0][dt.Columns.Count - 1] = DateTime.Parse(dt.Rows[0]["DET_KPRQ"].ToString()).ToString("MM"); 
                dt.Columns.Add("day");
                dt.Rows[0][dt.Columns.Count - 1] = DateTime.Parse(dt.Rows[0]["DET_KPRQ"].ToString()).ToString("dd");

                string[] je = dt.Rows[0]["DET_SFJE"].ToString().Trim().Split('.');
                if (je[0].Trim().Length > 3)
                {
                    dt.Columns.Add("qian");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(0, 1);
                    dt.Columns.Add("bai");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(1, 1);
                    dt.Columns.Add("shi");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(2, 1);
                    dt.Columns.Add("yuan");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(3, 1);

                    dt.Columns.Add("dxqian");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(0, 1));
                    dt.Columns.Add("dxbai");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(1, 1));
                    dt.Columns.Add("dxshi");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(2, 1));
                    dt.Columns.Add("dxyuan");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(3, 1));
                }
                else if (je[0].Trim().Length > 2)
                {
                    dt.Columns.Add("qian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("bai");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(0, 1);
                    dt.Columns.Add("shi");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(1, 1);
                    dt.Columns.Add("yuan");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(2, 1);

                    dt.Columns.Add("dxqian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxbai");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(0, 1));
                    dt.Columns.Add("dxshi");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(1, 1));
                    dt.Columns.Add("dxyuan");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(2, 1));
                }
                else if (je[0].Trim().Length > 1)
                {
                    dt.Columns.Add("qian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("bai");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("shi");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(0, 1);
                    dt.Columns.Add("yuan");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(1, 1);

                    dt.Columns.Add("dxqian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxbai");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxshi");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(0, 1));
                    dt.Columns.Add("dxyuan");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(1, 1));
                }
                else if (je[0].Trim().Length > 0)
                {
                    dt.Columns.Add("qian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("bai");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("shi");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("yuan");
                    dt.Rows[0][dt.Columns.Count - 1] = je[0].Substring(0, 1);

                    dt.Columns.Add("dxqian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxbai");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxshi");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxyuan");
                    dt.Rows[0][dt.Columns.Count - 1] = zh(je[0].Substring(0, 1));
                }
                else
                {
                    dt.Columns.Add("qian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("bai");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("shi");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("yuan");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";

                    dt.Columns.Add("dxqian");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxbai");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxshi");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                    dt.Columns.Add("dxyuan");
                    dt.Rows[0][dt.Columns.Count - 1] = "/";
                }
               
                dt.Columns.Add("jiao");
                dt.Rows[0][dt.Columns.Count - 1] = je[1].Substring(0, 1);
                dt.Columns.Add("fen");
                dt.Rows[0][dt.Columns.Count - 1] = je[1].Substring(1, 1);

                dt.Columns.Add("dxjiao");
                dt.Rows[0][dt.Columns.Count - 1] = zh(je[1].Substring(0, 1));
                dt.Columns.Add("dxfen");
                dt.Rows[0][dt.Columns.Count - 1] = zh(je[1].Substring(1, 1));

                dt.Columns.Add("jebz");
                dt.Rows[0][dt.Columns.Count - 1] = dt.Rows[0]["DET_SFJE"].ToString().Trim() +" ("+ dt.Rows[0]["DET_SFFS"].ToString().Trim()+")";


                dt.Columns.Add("xm");
                dt.Rows[0][dt.Columns.Count - 1] = dt.Rows[0]["DET_SFXM"].ToString().Trim();
               
            }

            return dt;
        }
        private void 设计票据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Url = System.Windows.Forms.Application.StartupPath + "\\收费.frx";
            frt.Load(Url);
            frt.RegisterData(PrintData(lsh), "new");
            frt.GetDataSource("new").Enabled = true;
            frt.Design();
        }

        public void  gxkpzt(string ls)
        {
            Query mqury = new Query();
            string sql = string.Format(@"update Detects_SJA set DET_SFKP=0 where DETECT_ID='{0}'", ls);
            mqury.UpdateTable(sql);
        }

        public string zh(string num)
        {
            string dx = "";
            string[] ziduan = { "零","壹","贰","叁","肆","伍","陆","柒","捌","玖"};
            string[] zi = { "0","1","2","3","4","5","6","7","8","9", };
            for (int i = 0; i < ziduan.Length; i++)
            {
                if (num.Contains(zi[i]))
                {
                    dx = ziduan[i].Trim();
                }
            }
            return dx;
        }

        private void 打印票据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Url = System.Windows.Forms.Application.StartupPath + "\\收费.frx";
            frt.Load(Url);
            frt.RegisterData(PrintData(lsh), "new");
            frt.GetDataSource("new").Enabled = true;
            frt.Prepare();
            frt.PrintSettings.ShowDialog = false;
            frt.Print();
        }

        private void Chargemanagement_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F8)
            {
                设计票据ToolStripMenuItem.Visible = true;
            }
            else
            {
                设计票据ToolStripMenuItem.Visible = false;
            }
        }


     
    }
}
